﻿using NPOI.SS.UserModel;
using NPOI.XSSF.UserModel;
using System;
using System.Collections.Generic;
using System.Data;
using System.IO;
using System.Text;

namespace d1Project.Tools
{
    public static class ExcelHelper
    {
        public static byte[] Demo(DataTable dt)
        {
            IWorkbook workbook = new XSSFWorkbook();
            //创建sheet
            workbook.CreateSheet("Sheet1");
            //移除sheet
            workbook.RemoveSheetAt(0);
            //根据Sheet名字获得Sheet对象
            ISheet sheet = workbook.GetSheet("Sheet1");

            return null;
        }

        public static Byte[] ExportJoin(List<Byte[]> byteList, String strHeaderText, String strFileName)
        {
            XSSFWorkbook workBook = null;
            ISheet sheet = null;
            for (int i = 0; i < byteList.Count; i++)
            {
                if (i == 0)
                {
                    using (MemoryStream ms = new MemoryStream())
                    {
                        ms.Write(byteList[i], 0, byteList[i].Length);
                        workBook = new XSSFWorkbook(ms);
                        sheet = workBook.GetSheetAt(0);
                    }
                }
                else
                {
                    using (MemoryStream ms = new MemoryStream())
                    {
                        ms.Write(byteList[i], 0, byteList[i].Length);
                        var workBook2 = new XSSFWorkbook(ms);
                        var sheet2 = workBook2.GetSheetAt(0);
                        for (int j = sheet2.FirstRowNum; j <= sheet2.LastRowNum; j++)
                        {
                            var row = sheet.CreateRow(sheet.LastRowNum + j);
                            row = sheet2.GetRow(j);
                            //foreach (var item in sheet2.GetRow(j).Cells)
                            //{
                            //    row.Cells.Add(item);
                            //}
                            //var cells = sheet2.GetRow(j).Cells;
                            //row.Cells.AddRange(cells);
                        }
                    }
                }
            }
            using (MemoryStream ms = new MemoryStream())
            {
                workBook.Write(ms);
                return ms.ToArray();
            }
            //using (FileStream fs = new FileStream(strFileName, FileMode.Create, FileAccess.Write))
            //{

            //    workBook.Write(fs);
            //    fs.Flush();
            //    Byte[] bytes = new Byte[fs.Length];
            //    fs.Read(bytes, 0, bytes.Length);
            //    return bytes;
            //}
        }

        public static byte[] Export(DataTable dtSource, string strHeaderText)
        {
            //取得列宽
            int[] arrColWidth = new int[dtSource.Columns.Count];
            foreach (DataColumn item in dtSource.Columns)
            {
                arrColWidth[item.Ordinal] = Encoding.GetEncoding(936).GetBytes(item.ColumnName.ToString()).Length;
            }
            for (int i = 0; i < dtSource.Rows.Count; i++)
            {
                for (int j = 0; j < dtSource.Columns.Count; j++)
                {
                    int intTemp = Encoding.GetEncoding(936).GetBytes(dtSource.Rows[i][j].ToString()).Length;
                    if (intTemp > arrColWidth[j])
                    {
                        if (intTemp > 254)
                            arrColWidth[j] = 254;
                        else
                            arrColWidth[j] = intTemp;
                    }
                }
            }

            IWorkbook workbook = new XSSFWorkbook();
            ISheet sheet = workbook.CreateSheet();

            IRow headerRow = sheet.CreateRow(0);
            ICellStyle headStyle = workbook.CreateCellStyle();
            headStyle.Alignment = HorizontalAlignment.Center;
            IFont font = workbook.CreateFont();
            font.FontHeightInPoints = 10;
            font.Boldweight = 700;
            headStyle.IsLocked = true;
            headStyle.SetFont(font);
            foreach (DataColumn column in dtSource.Columns)
            {
                ICell cell = headerRow.CreateCell(column.Ordinal);
                cell.SetCellValue(column.ColumnName);
                cell.CellStyle = headStyle;
                //设置列宽
                if (column.DataType.ToString() == "ZCloud.Excel.ExcelImage")
                {
                    sheet.SetColumnWidth(column.Ordinal, 20 * 256);
                }
                else
                {
                    sheet.SetColumnWidth(column.Ordinal, (arrColWidth[column.Ordinal] + 1) * 256);
                }
            }
            //冻结第一列
            sheet.CreateFreezePane(0, 1, 0, dtSource.Columns.Count - 1);

            //日期样式
            ICellStyle dateStyle = workbook.CreateCellStyle();
            IDataFormat format = workbook.CreateDataFormat();
            dateStyle.DataFormat = format.GetFormat("yyyy年m月d日");

            //超级链接样式
            ////cell style for hyperlinks
            ////by default hyperlinks are blue and underlined
            ICellStyle hlink_style = workbook.CreateCellStyle();
            IFont hlink_font = workbook.CreateFont();
            hlink_font.Underline = FontUnderlineType.Single;
            hlink_font.Color = NPOI.HSSF.Util.HSSFColor.Blue.Index;
            hlink_style.SetFont(hlink_font);

            IDrawing patriarch = sheet.CreateDrawingPatriarch();

            for (int i = 0; i < dtSource.Rows.Count; i++)
            {
                var row = dtSource.Rows[i];
                IRow dataRow = sheet.CreateRow(i + 1);
                foreach (DataColumn column in dtSource.Columns)
                {
                    ICell newCell = dataRow.CreateCell(column.Ordinal);
                    //string drValue = row[column].ToString();
                    switch (column.DataType.ToString())
                    {
                        case "System.String"://字符串类型
                            newCell.SetCellValue(row[column].ToString());
                            break;
                        case "System.DateTime"://日期类型
                            newCell.SetCellValue(DateTime.Parse(row[column].ToString()));
                            //newCell.CellStyle = dateStyle;//格式化显示
                            break;
                        case "System.Boolean"://布尔型
                            newCell.SetCellValue(bool.Parse(row[column].ToString()));
                            break;
                        case "System.Int16"://整型
                        case "System.Int32":
                        case "System.Int64":
                        case "System.Byte":
                            newCell.SetCellValue(int.Parse(row[column].ToString()));
                            break;
                        case "System.Single"://浮点型
                        case "System.Decimal":
                        case "System.Double":
                            //double doubV = 0;
                            //double.TryParse(row[column].ToString(), out doubV);
                            newCell.SetCellValue(double.Parse(row[column].ToString()));
                            break;
                        case "System.DBNull"://空值处理
                            newCell.SetCellValue("");
                            break;
                        case "ZCloud.Excel.ExcelLink": //超级链接
                            ExcelLink eurl = row[column] as ExcelLink;
                            newCell.SetCellValue(eurl.Name);
                            XSSFHyperlink link = new XSSFHyperlink(HyperlinkType.Url);
                            link.Address = eurl.Url;
                            newCell.Hyperlink = (link);
                            newCell.CellStyle = (hlink_style);
                            break;
                        case "ZCloud.Excel.ExcelImage": //图片
                            var img = row[column] as ExcelImage;
                            if (img.Buffer == null || img.Buffer.Length == 0)
                            {
                                newCell.SetCellValue("");
                            }
                            else
                            {
                                dataRow.Height = 100 * 20;
                                XSSFClientAnchor anchor = new XSSFClientAnchor(0, 0, 0, 0, column.Ordinal, i + 1, column.Ordinal + 1, i + 2);
                                patriarch.CreatePicture(anchor, workbook.AddPicture(img.Buffer, PictureType.JPEG));
                            }
                            break;
                        default:
                            newCell.SetCellValue("");
                            break;
                    }
                }
            }
            using (MemoryStream ms = new MemoryStream())
            {
                workbook.Write(ms);
                //ms.Flush();
                //ms.Position = 0;
                return ms.ToArray();
            }
        }

        public static DataTable Import(Stream stream)
        {
            DataTable dt = new DataTable();
            IWorkbook hssfworkbook = WorkbookFactory.Create(stream);
            var sheet = hssfworkbook.GetSheetAt(0);
            System.Collections.IEnumerator rows = sheet.GetRowEnumerator();
            var headerRow = sheet.GetRow(0);
            int cellCount = headerRow.LastCellNum;
            for (int j = 0; j < cellCount; j++)
            {
                var cell = headerRow.GetCell(j);
                dt.Columns.Add(cell.ToString().Trim());
            }
            for (int i = (sheet.FirstRowNum + 1); i <= sheet.LastRowNum; i++)
            {
                var row = sheet.GetRow(i);
                DataRow dataRow = dt.NewRow();
                for (int j = row.FirstCellNum; j < cellCount; j++)
                {
                    if (row.GetCell(j) != null)
                        dataRow[j] = row.GetCell(j).ToString();
                }
                dt.Rows.Add(dataRow);
            }
            return dt;
        }
    }
}
